﻿Option Strict On

Imports Sunts.DataLayer.SQLite
Imports System.Data.SQLite

Imports System.Net

Imports System.Net.Mail
Imports System.IO

Public Class MailRecipient
    Public Property MailRecipientID As Int32
    Public Property Recipient As String
    Public Property EmailAddress As String

    Public Function Insert(ConnectionString As String) As Int32
        Try
            Using sdl As New SQLiteDataLayer(ConnectionString)
                sdl.CommandText = "INSERT INTO MailRecipients (Recipient, EmailAddress) VALUES (@Recipient, @EmailAddress )"

                sdl.ParameterCollection = New SQLiteParamCollection
                sdl.ParameterCollection.Add(New SQLiteParameter("@Recipient", Me.Recipient))
                sdl.ParameterCollection.Add(New SQLiteParameter("@EmailAddress", Me.EmailAddress))

                Insert = sdl.ExecuteNonQuery()
            End Using
        Catch ex As Exception
            Throw New Exception("MailRecipient: Insert()" + ControlChars.CrLf + ex.Message + " " + ex.Source + ex.StackTrace)
        End Try

    End Function

    Public Shared Function SelectAll(ConnectioNString As String) As MailRecipientCollection
        Dim MailRecipientCollection As MailRecipientCollection
        Dim MailRecipient As MailRecipient
        Dim Reader As SQLiteDataReader

        Try
            Using sdl As New SQLiteDataLayer(ConnectioNString)
                sdl.CommandText = "SELECT MailRecipientID, Recipient, EmailAddress FROM MailRecipients"

                MailRecipientCollection = New MailRecipientCollection

                Reader = sdl.ExecuteDataReader

                While Reader.Read
                    MailRecipient = New MailRecipient
                    MailRecipient.MailRecipientID = CInt(Reader("MailRecipientID"))
                    MailRecipient.Recipient = CStr(Reader("Recipient"))
                    MailRecipient.EmailAddress = CStr(Reader("EmailAddress"))
                    MailRecipientCollection.Add(MailRecipient)
                End While
                Reader.Close()
            End Using

            SelectAll = MailRecipientCollection

        Catch ex As Exception
            Throw New Exception("MailRecipient: SelectAll()" + ControlChars.CrLf + ex.Message + " " + ex.Source + ex.StackTrace)
        Finally
            MailRecipient = Nothing
            MailRecipientCollection = Nothing
            Reader = Nothing
        End Try
    End Function

    Public Shared Function SelectByBackupJobID(BackupJobID As Int32, ConnectionString As String) As MailRecipientCollection
        Dim MailRecipientCollection As MailRecipientCollection
        Dim MailRecipient As MailRecipient
        Dim Reader As SQLiteDataReader

        Try
            Using sdl As New SQLiteDataLayer(ConnectionString)
                sdl.CommandText = "SELECT MailRecipients.MailRecipientID, Recipient, EmailAddress, BackupJobID FROM MailRecipients " & _
                                    "INNER JOIN MailJobs ON MailJobs.MailRecipientID = MailRecipients.MailRecipientID " & _
                                    "WHERE BackupJobID = @BackupJobID"

                sdl.ParameterCollection = New SQLiteParamCollection
                sdl.ParameterCollection.Add(New SQLiteParameter("@BackupJobID", BackupJobID))

                MailRecipientCollection = New MailRecipientCollection

                Reader = sdl.ExecuteDataReader

                While Reader.Read
                    MailRecipient = New MailRecipient
                    MailRecipient.MailRecipientID = CInt(Reader("MailRecipientID"))
                    MailRecipient.Recipient = CStr(Reader("Recipient"))
                    MailRecipient.EmailAddress = CStr(Reader("EmailAddress"))
                    MailRecipientCollection.Add(MailRecipient)
                End While
                Reader.Close()
            End Using

            SelectByBackupJobID = MailRecipientCollection

        Catch ex As Exception
            Throw New Exception("MailRecipient: SelectByBackupJobID()" + ControlChars.CrLf + ex.Message + " " + ex.Source + ex.StackTrace)
        Finally
            MailRecipient = Nothing
            MailRecipientCollection = Nothing
            Reader = Nothing
        End Try
    End Function

    Public Function Delete(ConnectionString As String) As Int32
        Try
            Using sdl As New SQLiteDataLayer(ConnectionString)
                sdl.CommandText = "DELETE FROM MailRecipients WHERE MailRecipientID = @MailRecipientID )"

                sdl.ParameterCollection = New SQLiteParamCollection
                sdl.ParameterCollection.Add(New SQLiteParameter("@MailRecipientID", Me.MailRecipientID))

                Delete = sdl.ExecuteNonQuery()
            End Using
        Catch ex As Exception
            Throw New Exception("MailRecipient: Delete()" + ControlChars.CrLf + ex.Message + " " + ex.Source + ex.StackTrace)
        End Try
    End Function

    Public Function Update(ConnectionString As String) As Int32
        Try
            Using sdl As New SQLiteDataLayer(ConnectionString)
                sdl.CommandText = "UPDATE MailRecipients SET " & _
                    "Recipient = @Recipient, " & _
                    "EmailAddress = @EmailAddress " & _
                    "WHERE MailRecipientID = @MailRecipientID"


                sdl.ParameterCollection = New SQLiteParamCollection
                sdl.ParameterCollection.Add(New SQLiteParameter("@MailRecipientID", Me.MailRecipientID))
                sdl.ParameterCollection.Add(New SQLiteParameter("@Recipient", Me.Recipient))
                sdl.ParameterCollection.Add(New SQLiteParameter("@EmailAddress", Me.EmailAddress))


                Update = sdl.ExecuteNonQuery()
            End Using
        Catch ex As Exception
            Throw New Exception("MailRecipient: Update()" + ControlChars.CrLf + ex.Message + " " + ex.Source + ex.StackTrace)
        End Try
    End Function
End Class

Public Class MailRecipientCollection
    Inherits System.Collections.ObjectModel.Collection(Of MailRecipient)
End Class

Public Class MailServer
    Public Property MailServerID As Int32
    Public Property MailServer As String
    Public Property Port As Int32
    Public Property SenderName As String
    Public Property SenderEmailAddress As String
    Public Property SMTPAuthentication As Boolean
    Public Property SMTPUsername As String
    Public Property SMTPPassword As String

    Public Function Insert(ConnectionString As String) As Int32
        Try
            Using sdl As New SQLiteDataLayer(ConnectionString)
                sdl.CommandText = "INSERT INTO MailServer (MailServer, Port, SenderName, SenderEmailAddress, SMTPAuthentication, SMTPUsername, SMTPPassword) " & _
                                    "VALUES (@MailServer, @Port, @SenderName, @SenderEmailAddress, @SMTPAuthentication, @SMTPUsername, @SMTPPassword)"

                sdl.ParameterCollection = New SQLiteParamCollection
                sdl.ParameterCollection.Add(New SQLiteParameter("@MailServer", Me.MailServer))
                sdl.ParameterCollection.Add(New SQLiteParameter("@Port", Me.Port))
                sdl.ParameterCollection.Add(New SQLiteParameter("@SenderName", Me.SenderName))
                sdl.ParameterCollection.Add(New SQLiteParameter("@SenderEmailAddress", Me.SenderEmailAddress))
                sdl.ParameterCollection.Add(New SQLiteParameter("@SMTPAuthentication", Me.SMTPAuthentication))
                sdl.ParameterCollection.Add(New SQLiteParameter("@SMTPUsername", Me.SMTPUsername))
                sdl.ParameterCollection.Add(New SQLiteParameter("@SMTPPassword", Me.SMTPPassword))

                Insert = sdl.ExecuteNonQuery()
            End Using
        Catch ex As Exception
            Throw New Exception("MailServer: Insert()" + ControlChars.CrLf + ex.Message + " " + ex.Source + ex.StackTrace)
        End Try

    End Function
    Public Function Update(ConnectionString As String) As Int32
        Try
            Using sdl As New SQLiteDataLayer(ConnectionString)
                sdl.CommandText = "UPDATE MailServer SET " & _
                    "MailServer = @MailServer, " & _
                    "Port = @Port, " & _
                    "SenderName = @SenderName, " & _
                    "SenderEmailAddress = @SenderEmailAddress, " & _
                    "SMTPAuthentication = @SMTPAuthentication, " & _
                    "SMTPUsername = @SMTPUsername, " & _
                    "SMTPPassword = @SMTPPassword " & _
                    "WHERE MailServerID = @MailServerID"


                sdl.ParameterCollection = New SQLiteParamCollection
                sdl.ParameterCollection.Add(New SQLiteParameter("@MailServerID", Me.MailServerID))
                sdl.ParameterCollection.Add(New SQLiteParameter("@MailServer", Me.MailServer))
                sdl.ParameterCollection.Add(New SQLiteParameter("@Port", Me.Port))
                sdl.ParameterCollection.Add(New SQLiteParameter("@SenderName", Me.SenderName))
                sdl.ParameterCollection.Add(New SQLiteParameter("@SenderEmailAddress", Me.SenderEmailAddress))
                sdl.ParameterCollection.Add(New SQLiteParameter("@SMTPAuthentication", Me.SMTPAuthentication))
                sdl.ParameterCollection.Add(New SQLiteParameter("@SMTPUsername", Me.SMTPUsername))
                sdl.ParameterCollection.Add(New SQLiteParameter("@SMTPPassword", Me.SMTPPassword))

                Update = sdl.ExecuteNonQuery()
            End Using
        Catch ex As Exception
            Throw New Exception("MailServer: Insert()" + ControlChars.CrLf + ex.Message + " " + ex.Source + ex.StackTrace)
        End Try
    End Function
    Public Shared Function SelectAll(ConnectionString As String) As MailServerCollection
        Dim MailServerCollection As MailServerCollection
        Dim MailServer As MailServer
        Dim Reader As SQLiteDataReader

        Try
            Using sdl As New SQLiteDataLayer(ConnectionString)
                sdl.CommandText = "SELECT MailServerID, MailServer, Port, SenderName, SMTPAuthentication, SenderEmailAddress, SMTPUsername, SMTPPassword FROM MailServer LIMIT 1"

                MailServerCollection = New MailServerCollection

                Reader = sdl.ExecuteDataReader

                While Reader.Read
                    MailServer = New MailServer
                    MailServer.MailServerID = CInt(Reader("MailServerID"))
                    MailServer.MailServer = CStr(Reader("MailServer"))
                    MailServer.Port = CInt(Reader("Port"))
                    MailServer.SenderName = CStr(Reader("SenderName"))
                    MailServer.SMTPAuthentication = CBool(Reader("SMTPAuthentication"))
                    MailServer.SenderEmailAddress = CStr(Reader("SenderEmailAddress"))
                    MailServer.SMTPUsername = CStr(Reader("SMTPUsername"))
                    MailServer.SMTPPassword = CStr(Reader("SMTPPassword"))
                    MailServerCollection.Add(MailServer)
                End While
                Reader.Close()
            End Using

            SelectAll = MailServerCollection

        Catch ex As Exception
            Throw New Exception("MailServer: SelectAll()" + ControlChars.CrLf + ex.Message + " " + ex.Source + ex.StackTrace)
        Finally
            MailServer = Nothing
            MailServerCollection = Nothing
            Reader = Nothing
        End Try
    End Function
End Class

Public Class MailServerCollection
    Inherits System.Collections.ObjectModel.Collection(Of MailServer)
End Class

Public Class MailJob
    Public Property MailRecipientID As Int32
    Public Property BackupJobID As Int32

    Public Function Insert(ConnectionString As String) As Int32
        Try
            Using sdl As New SQLiteDataLayer(ConnectionString)
                sdl.CommandText = "INSERT INTO MailJobs (MailRecipientID, BackupJobID) VALUES (@MailRecipientID, @BackupJobID )"

                sdl.ParameterCollection = New SQLiteParamCollection
                sdl.ParameterCollection.Add(New SQLiteParameter("@MailRecipientID", Me.MailRecipientID))
                sdl.ParameterCollection.Add(New SQLiteParameter("@BackupJobID", Me.BackupJobID))

                Insert = sdl.ExecuteNonQuery()
            End Using
        Catch ex As Exception
            Throw New Exception("MailJob: Insert()" + ControlChars.CrLf + ex.Message + " " + ex.Source + ex.StackTrace)
        End Try

    End Function

End Class

Public Module MailFunctions
    Public Function CreateHeaderRow(ByVal ServerName As String) As String
        'create first row with headers
        CreateHeaderRow = "" & _
        "<html>" & _
        "<body bgcolor='#CECF9C' text='#FFFFFF' topmargin='10' leftmargin='0'>" & _
        "<center>" & _
        "<table width ='90%' cellpadding='0' style='border:1px solid #000000; color:#000000; padding-left:4px; padding-right:4px; padding-top:1px; padding-bottom:1px; font-family:Verdana; font-size:10px' cellspacing='0' border='0'>" & _
        "<tr style='border-left:1px solid #000000; border-right:1px solid #000000; border-top:1px solid #000000; background-color: #003366; padding-left: 8px; padding-top: 8px; padding-bottom: 8px;'> " & _
        "<td><b><font size='2' face='Verdana' color='#FFFFFF'>Hyper-V Backup Report - " & ServerName & " - " & Now() & "</font></b></td>" & _
        "</tr>"
    End Function
    Public Function CreateJobLog(ByVal LogName As String) As String
        CreateJobLog = ""
        Using sr As StreamReader = New StreamReader(My.Application.Info.DirectoryPath & "\Logs\" & LogName)
            Do While sr.Peek() > 0
                CreateJobLog = CreateJobLog & "" & _
                "<tr style='font-family:verdana; font-size:12px'>" & _
                "<td style='border-top:1px solid #E7EBFF; background-color:#FFFFFF;' width='50%' bordercolor='#E7EBFF'><font size='2'>" & sr.ReadLine & "</td>" & _
                "</tr>"
            Loop
        End Using
    End Function
    Public Function CreateFooterRow() As String
        CreateFooterRow = "" & _
        "</table>" & _
        "</center>" & _
        "</body>" & _
        "</html>"
    End Function

    Public Function SendTestMail(ByVal MailServer As MailServer, ByVal MailRecipient As MailRecipient) As Boolean
        Dim netcred As New NetworkCredential(MailServer.SMTPUsername, MailServer.SMTPPassword)
        'TODO cleanup exception code
        Try
            Using SmtpClient As New SmtpClient(MailServer.MailServer, MailServer.Port)
                Using MailMessage As New MailMessage
                    MailMessage.To.Add(New MailAddress(MailRecipient.EmailAddress))
                    MailMessage.From = New MailAddress(MailServer.SenderEmailAddress, MailServer.SenderName)
                    MailMessage.Subject = "Hyper-V Backup Test Message"
                    MailMessage.Body = "This is a test message from Hyper-V backup, if you are reading this then the mail configuration is complete"
                    MailMessage.IsBodyHtml = True

                    If MailServer.SMTPAuthentication = True Then SmtpClient.Credentials = netcred

                    SmtpClient.Send(MailMessage)
                End Using
            End Using

            Return True
        Catch ex As Exception
            Return False
        Finally
            netcred = Nothing
        End Try
    End Function

    Public Function SendMail(ByVal MailServer As MailServer, ByVal MailRecipient As MailRecipient, Message As String) As Boolean
        Dim netcred As New NetworkCredential(MailServer.SMTPUsername, MailServer.SMTPPassword)
        'TODO cleanup exception code
        Try
            Using SmtpClient As New SmtpClient(MailServer.MailServer, MailServer.Port)
                Using MailMessage As New MailMessage
                    MailMessage.To.Add(New MailAddress(MailRecipient.EmailAddress))
                    MailMessage.From = New MailAddress(MailServer.SenderEmailAddress, MailServer.SenderName)
                    MailMessage.Subject = "Hyper-V Backup Report"
                    MailMessage.Body = Message
                    MailMessage.IsBodyHtml = True

                    If MailServer.SMTPAuthentication = True Then SmtpClient.Credentials = netcred

                    SmtpClient.Timeout = 30000
                    SmtpClient.Send(MailMessage)
                End Using
            End Using

            Return True
        Catch ex As Exception
            Throw ex
        Finally
            netcred = Nothing
        End Try
    End Function




End Module